package com.lyzd.dbtools.dao;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.lyzd.dbtools.entity.Column;
import com.lyzd.dbtools.entity.DataBase;
import com.lyzd.dbtools.entity.Index;
import com.lyzd.dbtools.entity.Table;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class MySqlDAO {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 获取所有数据库
    public List<DataBase> queryAllDataBase() {
        final String selectText = "SELECT \n" +
                "  `CATALOG_NAME`,\n" +
                "  `SCHEMA_NAME`,\n" +
                "  `DEFAULT_CHARACTER_SET_NAME`,\n" +
                "  `DEFAULT_COLLATION_NAME`,\n" +
                "  `SQL_PATH` \n" +
                "FROM\n" +
                "  `information_schema`.`SCHEMATA` \n" +
                "where `SCHEMA_NAME` not in (\n" +
                "    'information_schema',\n" +
                "    'mysql',\n" +
                "    'performance_schema',\n" +
                "    'sys'\n" +
                "  )";
        return jdbcTemplate.query(selectText, new Object[0],
                (rs, rowNum) -> DataBase.builder().catalogName(rs.getString("CATALOG_NAME"))
                        .schemaName(rs.getString("SCHEMA_NAME"))
                        .defaultCharacterSetName(rs.getString("DEFAULT_CHARACTER_SET_NAME"))
                        .defaultCollationName(rs.getString("DEFAULT_COLLATION_NAME"))
                        .sqlPath(rs.getString("SQL_PATH")).build()
        );
    }

    // 获取单个数据库的数据表
    public List<Table> queryTablesByDbName(String dbName) {
        final String selectText = "SELECT \n" +
                "  `TABLE_CATALOG`,\n" +
                "  `TABLE_SCHEMA`,\n" +
                "  `TABLE_NAME`,\n" +
                "  `TABLE_TYPE`,\n" +
                "  `ENGINE`,\n" +
                "  `VERSION`,\n" +
                "  `ROW_FORMAT`,\n" +
                "  `TABLE_ROWS`,\n" +
                "  `AVG_ROW_LENGTH`,\n" +
                "  `DATA_LENGTH`,\n" +
                "  `MAX_DATA_LENGTH`,\n" +
                "  `INDEX_LENGTH`,\n" +
                "  `DATA_FREE`,\n" +
                "  `AUTO_INCREMENT`,\n" +
                "  `CREATE_TIME`,\n" +
                "  `UPDATE_TIME`,\n" +
                "  `CHECK_TIME`,\n" +
                "  `TABLE_COLLATION`,\n" +
                "  `CHECKSUM`,\n" +
                "  `CREATE_OPTIONS`,\n" +
                "  `TABLE_COMMENT` \n" +
                "FROM\n" +
                "  `information_schema`.`TABLES`\n" +
                "WHERE `TABLE_SCHEMA`=?\n" +
                "AND `TABLE_NAME` <> 'flyway_schema_history'";
        return jdbcTemplate.query(selectText, new Object[]{dbName},
                (rs, rowNum) -> Table.builder().tableCatalog(rs.getString("TABLE_CATALOG"))
                        .tableSchema(rs.getString("TABLE_SCHEMA"))
                        .tableName(rs.getString("TABLE_NAME"))
                        .tableType(rs.getString("TABLE_TYPE"))
                        .engine(rs.getString("ENGINE"))
                        .version(rs.getObject("VERSION") != null ? rs.getInt("VERSION") : null)
                        .rowFormat(rs.getString("ROW_FORMAT"))
                        .tableRows(rs.getObject("TABLE_ROWS") != null ? rs.getInt("TABLE_ROWS") : null)
                        .avgRowLength(rs.getObject("AVG_ROW_LENGTH") != null ? rs.getInt("AVG_ROW_LENGTH") : null)
                        .dataLength(rs.getObject("DATA_LENGTH") != null ? rs.getInt("DATA_LENGTH") : null)
                        .maxDataLength(rs.getObject("MAX_DATA_LENGTH") != null ? rs.getInt("MAX_DATA_LENGTH") : null)
                        .indexLength(rs.getObject("INDEX_LENGTH") != null ? rs.getInt("INDEX_LENGTH") : null)
                        .dataFree(rs.getObject("DATA_FREE") != null ? rs.getInt("DATA_FREE") : null)
                        .autoIncrement(rs.getObject("AUTO_INCREMENT") != null ? rs.getInt("AUTO_INCREMENT") : null)
                        .createTime(rs.getObject("CREATE_TIME") != null ? DateUtil.toLocalDateTime(rs.getDate("CREATE_TIME")) : null)
                        .updateTime(rs.getObject("UPDATE_TIME") != null ? DateUtil.toLocalDateTime(rs.getDate("UPDATE_TIME")) : null)
                        .checkTime(rs.getObject("CHECK_TIME") != null ? DateUtil.toLocalDateTime(rs.getDate("CHECK_TIME")) : null)
                        .tableCollation(rs.getString("TABLE_COLLATION"))
                        .checksum(rs.getObject("CHECKSUM") != null ? rs.getInt("CHECKSUM") : null)
                        .createOptions(rs.getString("CREATE_OPTIONS"))
                        .tableComment(rs.getString("TABLE_COMMENT"))
                        .build()
        );
    }

    // 获取数据表的列
    public List<Column> queryColumnByDbAndTableName(String dbName, String tableName) {
        return jdbcTemplate.query(StrUtil.format("SHOW FULL COLUMNS FROM `{}`.`{}`", dbName, tableName), new Object[0],
                (rs, rowNum) -> Column.builder()
                        .filed(rs.getString("Field"))
                        .type(rs.getString("Type"))
                        .collation(rs.getString("Collation"))
                        .isNull(StrUtil.equals("YES", rs.getString("Null")) ? "是" : "否")
                        .key(rs.getString("Key"))
                        .defaultValue(rs.getString("Default"))
                        .extra(rs.getString("Extra"))
                        .privileges(rs.getString("Privileges"))
                        .comment(rs.getString("Comment"))
                        .build()
        );
    }

    // 获取数据表的索引
    public List<Index> queryIndexByDbAndTableName(String dbName, String tableName) {
        return jdbcTemplate.query(StrUtil.format("SHOW INDEX FROM `{}`.`{}`", dbName, tableName), new Object[0],
                (rs, rowNum) -> Index.builder()
                        .keyName("PRIMARY".equals(rs.getString("Key_name")) ? "主索引" : rs.getString("Key_name"))
                        .columnName(rs.getString("Column_name"))
                        .nonUnique(1 == rs.getInt("Non_unique") ? StrUtil.EMPTY : "unique")
                        .build()
        );
    }
}
